package com.lc.cn.managersys.common;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.Map;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

/**
 * modify JdbcTemplate's insert method to get the return key
 * @author luo_guang
 *
 */
public class JdbcTemplate extends org.springframework.jdbc.core.JdbcTemplate {

	private Log log = LogFactory.getLog(JdbcTemplate.class);
	
	/**
	 * insert with return key value
	 * @param sql 
	 * @param args 
	 * @return
	 */
	public long insert(final String sql, final Object[] args) {
		KeyHolder keyHolder = new GeneratedKeyHolder();
		update(new PreparedStatementCreator() {

			@Override
			public PreparedStatement createPreparedStatement(Connection con)
					throws SQLException {
				PreparedStatement ps = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
				
				for (int i = 0; i < args.length; i++) {
					ps.setObject(i + 1, args[i]);
				}
				
				return ps;
			}
		}, keyHolder);
		
		long key = keyHolder.getKey().longValue();
		
		if (log.isDebugEnabled()) {
			log.debug(sql + " result key: " + key);
		}
		
		return key;
	}
	
	/**
	 * get the count
	 * @param sql
	 * @param params
	 * @return
	 */
	public int queryForCount(String sql, Object[] params) {
		String sqlStr = "SELECT COUNT(1) FROM (" + sql + ") t";
		return queryForInt(sqlStr, params);
	}
	
	/**
	 * page for query
	 * @param page
	 * @param sql
	 * @param params
	 * @return
	 */
	public Page queryForPage(Page page, String sql, Object[] params) {
		Object [] objs = null;
		if (params != null) {
			objs = new Object[params.length + 2];
			
			System.arraycopy(params, 0, objs, 0, params.length);
		}
		
		String sqlstr = "SELECT * FROM (" + sql + ") t limit ?, ?";
		int count = queryForCount(sql, params);
		page.setCount(count);
		
		int offset = (page.getCurPage() - 1) * page.getPageSize();
		objs[objs.length - 2] = offset;
		objs[objs.length - 1] = page.getPageSize();
		
		if (log.isDebugEnabled()) {
			log.debug(sqlstr);
			log.debug(objs);
			log.debug("count:" + count);
		}
		
		List<Map<String, Object>> list = queryForList(sqlstr, objs);
		page.setList(list);
		
		return page;
	}
	
}
